﻿using System;
using System.Data;
using System.Data.OleDb;

namespace Data.OleDb
{
    public class Transaction
    {
        // TODO: how am i going to work the connection strings
        // will probalby have multiple databases

        private string _connectionString;

        public Transaction(string connectionString)
        {
            _connectionString = connectionString;
        }

        public Transaction()
        {
            _connectionString = Database.GetConnectionString();
        }

        public bool Insert(string sqlStatement, OleDbParameter[] parameters)
        {
            bool executed = false;
            OleDbConnection cn = new OleDbConnection(_connectionString);
            OleDbCommand cmd = new OleDbCommand(sqlStatement, cn);

            foreach (var p in parameters)
                cmd.Parameters.Add(p);

            try
            {
                cn.Open();
                cmd.ExecuteNonQuery();

                cn.Close();
                executed = true;
            }
            catch (Exception ex)
            {

                throw ex;
            }

            // clean up
            cmd.Dispose();
            cn.Dispose();

            return executed;
        }
        public bool Update(string sqlStatement, OleDbParameter[] parameters)
        {
            bool executed = false;
            OleDbConnection cn = new OleDbConnection(_connectionString);
            OleDbCommand cmd = new OleDbCommand(sqlStatement, cn);

            foreach (var p in parameters)
                cmd.Parameters.Add(p);

            try
            {
                cn.Open();
                cmd.ExecuteNonQuery();

                cn.Close();
                executed = true;
            }
            catch (Exception ex)
            {
                throw ex;
            }

            // clean
            cmd.Dispose();
            cn.Dispose();

            return executed;
        }
        public DataRow GetRecord(string sqlStatement, params OleDbParameter[] parameters)
        {
            DataTable table = new DataTable();
            OleDbConnection cn = new OleDbConnection(_connectionString);
            OleDbCommand cmd = new OleDbCommand(sqlStatement, cn);

            if (parameters != null)
            {
                foreach (var p in parameters)
                    cmd.Parameters.Add(p);
            }

            OleDbDataAdapter adapter = new OleDbDataAdapter(cmd);


            try
            {
                adapter.Fill(table);

                cn.Close();
            }
            catch (Exception ex)
            {

                throw ex;
            }


            // clean
            adapter.Dispose();
            cmd.Dispose();
            cn.Dispose();


            return table.Rows.Count > 0 ? table.Rows[0] : null;
        }
        public DataTable GetRecords(string sqlStatement)
        {
            DataTable table = new DataTable();
            OleDbConnection cn = new OleDbConnection(_connectionString);
            OleDbCommand cmd = new OleDbCommand(sqlStatement);

            // need data adapter, then fill into datatable
            OleDbDataAdapter adapter = new OleDbDataAdapter(cmd.CommandText, cn);


            try
            {
                cn.Open();
                adapter.Fill(table);

                cn.Close();
            }
            catch (Exception ex)
            {
                throw ex;
            }

            // clean 
            adapter.Dispose();
            cmd.Dispose();
            cn.Dispose();

            return table;
        }
        public DataTable GetRecords(string sqlStatement, OleDbParameter[] parameters)
        {
            DataTable table = new DataTable();
            OleDbConnection cn = new OleDbConnection(_connectionString);
            OleDbCommand cmd = new OleDbCommand(sqlStatement);
            OleDbDataAdapter adapter = new OleDbDataAdapter(cmd.CommandText, cn);

            foreach (var p in parameters)
                cmd.Parameters.Add(p);

            try
            {
                adapter.Fill(table);

                cn.Close();
            }
            catch (Exception ex)
            {
                throw ex;
            }

            // clean
            adapter.Dispose();
            cmd.Dispose();
            cn.Dispose();

            return table;
        }
        public bool ExecuteSqlStatement(string sqlStatement)
        {
            bool executed = false;
            OleDbConnection cn = new OleDbConnection(_connectionString);
            OleDbCommand cmd = new OleDbCommand(sqlStatement, cn);

            try
            {
                cn.Open();
                cmd.ExecuteNonQuery();

                cn.Close();
                executed = true;
            }
            catch (Exception ex)
            {

                throw ex;
            }

            // clean up
            cmd.Dispose();
            cn.Dispose();

            return executed;
        }
        public bool ExecuteSqlStatements(params string[] statements)
        {
            bool executed = false;
            OleDbConnection cn = new OleDbConnection(_connectionString);
            OleDbCommand cmd = new OleDbCommand();
            cmd.Connection = cn;

            try
            {
                cn.Open();

                for (int i = 0; i < statements.Length; ++i)
                {
                    cmd.CommandText = statements[i];

                    try
                    {
                        cmd.ExecuteNonQuery();
                    }
                    catch (OleDbException ex)
                    {

                        if (ex.ErrorCode == -2147217865 || ex.ErrorCode == -2147217900)
                        {
                            // table does not exist do nothing 
                        }
                        else
                        {
                            throw ex;
                        }
                    }
                }

                cn.Close();
                executed = true;
            }
            catch (Exception ex)
            {

                throw ex;
            }

            // clean up
            cmd.Dispose();
            cn.Dispose();


            return executed;
        }
    }
}
